package com.zretc.emptraining.daoimpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.zretc.emptraining.dao.CCPTDao;
import com.zretc.emptraining.dbtools.DBTools;
import com.zretc.emptraining.dbtools.PageData;
import com.zretc.emptraining.entity.CCPT;

public class CCPTDaoImpl implements CCPTDao {
 /**
     * 获得课程计划、课程、教员联结的所有信息
	 * 分页:根据 课程名/教员/课程类型关键字  模糊查询
	 * 
	 * @param page     页码
	 * @param pagesize 每页显示的记录数
	 * @param keywords 课程名关键字
	 * @return PageData<CCPT> 课程信息集合
	 */
@Override
public PageData<CCPT> getCPTInfoByPage(int page, int pageSize, String keywords) {
	String sql = "select cpNo,courseplan.courseNo,courseStart,courseEnd,courseplan.teacherNo,\r\n" + 
			"courseNum,coursePlace,courseTime,courseStatus,course.`subJect`,courseName,\r\n" + 
			"courseDesc,coursePic,teacherName,teacherXl,teacherPic,teacherSex,teacherTel,teacherDesc \r\n" + 
			"FROM courseplan LEFT JOIN course \r\n" + 
			"on courseplan.courseNo=course.courseNo\r\n" + 
			"LEFT JOIN teach \r\n" + 
			"ON courseplan.teacherNo=teach.teacherNo\r\n" + 
			"where courseName LIKE ?\r\n" + 
			"OR course.`subJect` LIKE ?\r\n" + 
			"OR teacherName LIKE ?";
	PageData<CCPT> pageData = DBTools.exQueryByPage(sql, CCPT.class, page, pageSize, "%" + keywords + "%","%" + keywords + "%","%" + keywords + "%");
	return pageData;
}


/**
 * 得到所有课程计划表的课程类型（不重复）
 */
@Override
public List<String> getAllSubject() {
	String  sql="select distinct course.`subJect`\r\n" + 
			"FROM courseplan LEFT JOIN course \r\n" + 
			"ON courseplan.courseNo=course.courseNo";
	Connection conn=DBTools.getConnection();
	PreparedStatement pstmt=null;
	ResultSet rs=null;
	List<String>  list=new ArrayList<String>();
	try {
		pstmt=conn.prepareStatement(sql);
		rs=pstmt.executeQuery();
		while(rs.next()) {
			String  Subject=rs.getString(1);
			list.add(Subject);
		}
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}finally {
		try {
			rs.close();
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	return list;
}
/**
 * 获得最新开课时间 的4个课程信息
 */

@Override
public List<CCPT> getCPTInfo() {
	String sql="select cpNo,courseplan.courseNo,courseStart,courseEnd,courseplan.teacherNo,\r\n" + 
			"courseNum,coursePlace,courseTime,courseStatus,course.`subJect`,courseName,\r\n" + 
			"courseDesc,coursePic,teacherName,teacherXl,teacherPic,teacherSex,teacherTel,teacherDesc \r\n" + 
			"FROM courseplan LEFT JOIN course \r\n" + 
			"on courseplan.courseNo=course.courseNo\r\n" + 
			"LEFT JOIN teach \r\n" + 
			"ON courseplan.teacherNo=teach.teacherNo\r\n" + 
			"ORDER BY courseStart DESC LIMIT 5";
	List<CCPT>  list=(List<CCPT>) DBTools.exQuery(sql, CCPT.class);
	return list;
}
/**
 * 获得  员工-课程评价 的课程相关信息
 * 获得课程计划、课程、教员联结的所有信息
 * 分页
 * 
 * @param page     页码
 * @param pagesize 每页显示的记录数
 * @param empTel  员工手机号
 * @return PageData<CCPT> 课程信息集合
 */

@Override
public PageData<CCPT> getCPTInfoByEva(int page, int pagesize, String empTel) {
	String sql = "select courseplan.cpNo,courseplan.courseNo,courseStart,courseEnd,courseplan.teacherNo,\r\n" + 
			"courseNum,coursePlace,courseTime,courseStatus,course.`subJect`,courseName,\r\n" + 
			"courseDesc,coursePic,teacherName,teacherXl,teacherPic,teacherSex,teacherTel,teacherDesc\r\n" + 
			"FROM emp LEFT JOIN percourse \r\n" + 
			"on emp.empNo=percourse.empNo\r\n" + 
			"LEFT JOIN courseplan \r\n" + 
			"ON percourse.cpNo=courseplan.cpNo \r\n" + 
			"LEFT JOIN course \r\n" + 
			"on courseplan.courseNo=course.courseNo\r\n" + 
			"LEFT JOIN teach \r\n" + 
			"ON courseplan.teacherNo=teach.teacherNo\r\n" + 
			"WHERE emp.empTel=? AND percourse.applyState='1' AND courseplan.courseStatus=3";
	PageData<CCPT> pageData = DBTools.exQueryByPage(sql, CCPT.class, page, pagesize, empTel);
	return pageData;
}
/**
 * 教员-我的课程-课程信息 的课程相关信息
 * 获得课程计划、课程、教员联结的所有信息
 * 分页
 * 
 * @param page     页码
 * @param pagesize 每页显示的记录数
 * @param teacherTel 教员手机号
 * @param courseStatus 课程状态
 * @return PageData<CCPT> 课程信息集合
 */

@Override
public PageData<CCPT> getCPTInfoByTea(int page, int pagesize, String teacherTel, int courseStatus) {
	String sql = "select cpNo,courseplan.courseNo,courseStart,courseEnd,courseplan.teacherNo,\r\n" + 
			"courseNum,coursePlace,courseTime,courseStatus,course.`subJect`,courseName,\r\n" + 
			"courseDesc,coursePic,teacherName,teacherXl,teacherPic,teacherSex,teacherTel,teacherDesc \r\n" + 
			"FROM teach LEFT JOIN courseplan \r\n" + 
			"ON teach.teacherNo=courseplan.teacherNo\r\n" + 
			"LEFT JOIN course  \r\n" + 
			"ON courseplan.courseNo=course.courseNo\r\n" + 
			"WHERE teach.teacherTel=? AND courseplan.courseStatus=?";
	PageData<CCPT> pageData = DBTools.exQueryByPage(sql, CCPT.class, page, pagesize, teacherTel,courseStatus);
	return pageData;
}


@Override
public PageData<CCPT> getCPTInfoByEmp(int page, int pagesize, String empTel, String applyState) {
	String sql = "select courseplan.cpNo,courseplan.courseNo,courseStart,courseEnd,courseplan.teacherNo,\r\n" + 
			"courseNum,coursePlace,courseTime,courseStatus,course.`subJect`,courseName,\r\n" + 
			"courseDesc,coursePic,teacherName,teacherXl,teacherPic,teacherSex,teacherTel,teacherDesc\r\n" + 
			"FROM emp LEFT JOIN percourse \r\n" + 
			"on emp.empNo=percourse.empNo\r\n" + 
			"LEFT JOIN courseplan \r\n" + 
			"ON percourse.cpNo=courseplan.cpNo \r\n" + 
			"LEFT JOIN course \r\n" + 
			"on courseplan.courseNo=course.courseNo\r\n" + 
			"LEFT JOIN teach \r\n" + 
			"ON courseplan.teacherNo=teach.teacherNo\r\n" + 
			"WHERE emp.empTel=? AND percourse.applyState=?";
	PageData<CCPT> pageData = DBTools.exQueryByPage(sql, CCPT.class, page, pagesize, empTel,applyState);
	return pageData;
}

}
